﻿using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.POIFS;
using NPOI.SS.UserModel;
using NPOI.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;


namespace AF_Excel
{
    public class ExcelHelper
    {
        private static ExcelHelper _instance = null;
        public static ExcelHelper Instance
        {
            get
            {
                if (_instance == null)
                {
                    _instance = new ExcelHelper();
                }
                return _instance;
            }
        }
        private Dictionary<int, ICellStyle> CellStyleDic;
        public MemoryStream ListToExcel<T>(IList<T> list, List<OutputField> Fields, int outputSheetIndex = 0, string description = "", string templateFile = "")
        {
            //创建流对象
            MemoryStream ms = new MemoryStream();
            //床NOPI的相关对象
            IWorkbook workbook = null;
            ISheet sheet = null;
            if (!string.IsNullOrEmpty(templateFile))
            {
                FileStream fs = new FileStream(templateFile, FileMode.Open, FileAccess.Read);
                workbook = new HSSFWorkbook(fs);
                if (workbook.NumberOfSheets > 0)
                {
                    sheet = workbook.GetSheetAt(outputSheetIndex);
                }
                else
                {
                    sheet = workbook.CreateSheet();
                }
            }
            else
            {
                workbook = new HSSFWorkbook();
                sheet = workbook.CreateSheet();
            }
            IRow headerRow = sheet.CreateRow(0);
            if (list.Count > 0)
            {
                //通过反射得到对象的属性集合
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                //遍历属性集合生成excel的表头标题
                int colIndex = 0;
                foreach (OutputField item in Fields)
                {
                    ICell cell = null;

                    ICellStyle style = CreateStyle(workbook, item.HeadCellStyle);
                    cell = headerRow.CreateCell(colIndex);
                    cell.SetCellValue(item.FieldCaption);
                    cell.CellStyle = style;
                    colIndex++;
                }
                int rowIndex = 1;
                //遍历集合生成excel的行集数据
                for (int i = 0; i < list.Count; i++)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    colIndex = 0;
                    foreach (OutputField item in Fields)
                    {
                        ICell cell = dataRow.CreateCell(colIndex);
                        if (item.FieldName == "<RowNum>")
                        {
                            cell.SetCellValue(i + 1);
                            if (item.DataCellStyle != null)
                            {
                                ICellStyle style = CreateStyle(workbook, item.DataCellStyle);
                                cell.CellStyle = style;
                            }
                        }
                        else
                        {
                            if (!string.IsNullOrEmpty(item.DataCellFormula))
                            {
                                Regex reg = new Regex("<RN>");
                                if (reg.IsMatch(item.DataCellFormula))
                                {
                                    string newFormula = reg.Replace(item.DataCellFormula, (rowIndex + 1).ToString());
                                    cell.SetCellFormula(newFormula);
                                }
                                else
                                {
                                    cell.SetCellFormula(item.DataCellFormula);
                                }
                            }
                            else
                            {
                                PropertyInfo field = propertys.FirstOrDefault(p => p.Name == item.FieldName);
                                if (field != null)
                                {
                                    object obj = field.GetValue(list[i], null);
                                    if (obj != null)
                                    {
                                        string value = obj.ToString();
                                        value = value == "True" ? "是" : (value == "False" ? "否" : value);

                                        switch (item.DataType)
                                        {
                                            case EnumDataType.String:
                                                cell.SetCellValue(value);
                                                break;
                                            case EnumDataType.Int:
                                                int iValue = 0;
                                                if (int.TryParse(value, out iValue))
                                                {
                                                    cell.SetCellValue(iValue);
                                                }
                                                else
                                                {
                                                    cell.SetCellValue("ERR");
                                                }
                                                break;
                                            case EnumDataType.Float:
                                                float fValue = 0;
                                                if (float.TryParse(value, out fValue))
                                                {
                                                    cell.SetCellValue(fValue);
                                                }
                                                else
                                                {
                                                    cell.SetCellValue("ERR");
                                                }
                                                break;
                                            case EnumDataType.DataTime:
                                                DateTime dValue = DateTime.MinValue;
                                                if (DateTime.TryParse(value, out dValue))
                                                {
                                                    cell.SetCellValue(dValue);
                                                }
                                                else
                                                {
                                                    cell.SetCellValue("ERR");
                                                }
                                                break;
                                            default:
                                                break;
                                        }
                                    }
                                }
                                else
                                {
                                    new Exception("字段" + item.FieldName + "不存在或拼写错误，请检查!");
                                }
                            }
                            if (item.DataCellStyle != null)
                            {
                                ICellStyle style = CreateStyle(workbook, item.DataCellStyle);
                                cell.CellStyle = style;
                            }
                        }
                        colIndex++;
                    }
                    rowIndex++;
                }
                if (!string.IsNullOrEmpty(description))
                {
                    rowIndex = rowIndex + 1;
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    ICellStyle style = CreateStyle(workbook, new FieldStyle()
                    {
                        Alignment = EnumAlignment.Left,
                        FontColorIndex = NPOI.HSSF.Util.HSSFColor.Red.Index
                    });
                    ICell cell = dataRow.CreateCell(0);
                    cell.SetCellValue(description);
                    cell.CellStyle = style;
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 5));
                }
            }
            for (int i = 0; i <= Fields.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            return ms;
        }

        public DataTable ExcelToDataTable(string strFileName)
        {
            DataTable dt = new DataTable();

            IWorkbook hssfworkbook;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                try
                {

                    if (Path.GetExtension(strFileName) == ".xlsx")
                    {
                        hssfworkbook = new XSSFWorkbook(file);
                    }
                    else
                    {
                        hssfworkbook = new HSSFWorkbook(file);
                    }
                }
                catch (System.Exception E)
                {

                    throw E;
                }
            }
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++)
            {
                ICell cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }

            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        try
                        {
                            if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
                            {
                                dataRow[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
                            }
                            else
                            {
                                dataRow[j] = row.GetCell(j).ToString();
                            }
                        }
                        catch
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }

                    }
                }

                dt.Rows.Add(dataRow);
            }
            return dt;
        }

        /// <summary>
        /// 导入Excel
        /// </summary>
        /// <param name="strFileName">文件全名（含路径）</param>
        /// <param name="firstRowIsHead">是否包含表头</param>
        /// <param name="RowInputAction">行导入委托（数据，当前行Index，最大行数）</param>
        /// <returns></returns>
        public void InputExcel(string strFileName, bool firstRowIsHead, Action<List<object>, int, int> RowInputAction)
        {
            //所有时间日期格式，通过格式判断列的是不是日期
            List<int> DateTimeFormat = new List<int>() { 14, 31, 176, 177, 178, 180, 181, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 199, 200, 201, 203, 204, 205, 206, 207, 208, 209, 210 };
            IWorkbook hssfworkbook;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                try
                {

                    if (Path.GetExtension(strFileName) == ".xlsx")
                    {
                        hssfworkbook = new XSSFWorkbook(file);
                    }
                    else
                    {
                        hssfworkbook = new HSSFWorkbook(file);
                    }
                }
                catch (System.Exception E)
                {

                    throw E;
                }
            }
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;

            for (int i = (sheet.FirstRowNum + (firstRowIsHead ? 1 : 0)); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                List<object> RowItem = new List<object>();
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        if (DateTimeFormat.Count(p => p == row.GetCell(j).CellStyle.DataFormat) > 0)
                        {
                            RowItem.Add(row.GetCell(j).DateCellValue);
                        }
                        else
                        {
                            RowItem.Add(row.GetCell(j).ToString());
                        }
                    }
                    else
                    {
                        RowItem.Add("");
                    }
                }
                if (RowInputAction != null)
                {
                    RowInputAction(RowItem, i, sheet.LastRowNum - sheet.FirstRowNum);
                }
            }
        }

        private ICellStyle CreateStyle(IWorkbook workbook, FieldStyle Style)
        {
            if (CellStyleDic == null)
            {
                CellStyleDic = new Dictionary<int, ICellStyle>();
            }
            ICellStyle cellStyle = null;
            int StyleHashCode = Style.GetHashCode();
            if (CellStyleDic.Keys.Contains(StyleHashCode))
            {
                cellStyle = CellStyleDic[StyleHashCode];
            }
            else
            {
                cellStyle = (ICellStyle)workbook.CreateCellStyle();
                IFont font = workbook.CreateFont();
                if (Style != null)
                {
                    switch (Style.Alignment)
                    {
                        case EnumAlignment.Auto:
                            cellStyle.Alignment = HorizontalAlignment.General;
                            break;
                        case EnumAlignment.Left:
                            cellStyle.Alignment = HorizontalAlignment.Left;
                            break;
                        case EnumAlignment.Center:
                            cellStyle.Alignment = HorizontalAlignment.Center;
                            break;
                        case EnumAlignment.Right:
                            cellStyle.Alignment = HorizontalAlignment.Right;
                            break;
                        default:
                            break;
                    }
                    if (Style.BackColorIndex > 0)
                    {
                        cellStyle.FillPattern = FillPattern.SolidForeground;
                        cellStyle.FillForegroundColor = Style.BackColorIndex;
                    }
                    else
                    {
                        cellStyle.FillPattern = FillPattern.NoFill;
                    }
                    if (string.IsNullOrEmpty(Style.FontName))
                    {
                        Style.FontName = "微软雅黑";
                    }
                    if (Style.FontSize <= 0)
                    {
                        Style.FontSize = 11;
                    }
                    if (Style.FontColorIndex <= 0)
                    {
                        Style.FontColorIndex = 8;//黑色
                    }
                    if (Style.BorderLeft != BorderStyle.None)
                    {
                        cellStyle.BorderLeft = Style.BorderLeft;
                        cellStyle.LeftBorderColor = Style.BorderLeftColor;
                    }
                    if (Style.BorderTop != BorderStyle.None)
                    {
                        cellStyle.BorderTop = Style.BorderTop;
                        cellStyle.TopBorderColor = Style.BorderTopColor;
                    }
                    if (Style.BorderRight != BorderStyle.None)
                    {
                        cellStyle.BorderRight = Style.BorderRight;
                        cellStyle.RightBorderColor = Style.BorderRightColor;
                    }
                    if (Style.BorderBottom != BorderStyle.None)
                    {
                        cellStyle.BorderBottom = Style.BorderBottom;
                        cellStyle.BottomBorderColor = Style.BorderBottomColor;
                    }
                    if (Style.Border != BorderStyle.None)
                    {
                        cellStyle.BorderLeft = Style.Border;
                        cellStyle.LeftBorderColor = Style.BorderColor;
                        cellStyle.BorderTop = Style.Border;
                        cellStyle.TopBorderColor = Style.BorderColor;
                        cellStyle.BorderRight = Style.Border;
                        cellStyle.RightBorderColor = Style.BorderColor;
                        cellStyle.BorderBottom = Style.Border;
                        cellStyle.BottomBorderColor = Style.BorderColor;
                    }
                    if (!string.IsNullOrEmpty(Style.DataFormat))
                    {
                        IDataFormat dataFormat = workbook.CreateDataFormat();
                        cellStyle.DataFormat = dataFormat.GetFormat(Style.DataFormat);
                    }
                    font.FontName = Style.FontName;
                    font.FontHeightInPoints = Style.FontSize;
                    font.Color = Style.FontColorIndex;
                    cellStyle.SetFont(font);
                }
                CellStyleDic.Add(StyleHashCode, cellStyle);
            }
            return cellStyle;
        }

        public MemoryStream DataTableToExcel(DataTable DataTable, List<OutputField> Fields, int outputSheetIndex = 0, string description = "", string templateFile = "")
        {
            //创建流对象
            MemoryStream ms = new MemoryStream();
            //创建NOPI的相关对象
            IWorkbook workbook = null;
            ISheet sheet = null;
            if (!string.IsNullOrEmpty(templateFile))
            {
                FileStream fs = new FileStream(templateFile, FileMode.Open, FileAccess.Read);
                workbook = new HSSFWorkbook(fs);
                if (workbook.NumberOfSheets > 0)
                {
                    sheet = workbook.GetSheetAt(outputSheetIndex);
                }
                else
                {
                    sheet = workbook.CreateSheet();
                }
            }
            else
            {
                workbook = new HSSFWorkbook();
                sheet = workbook.CreateSheet();
            }
            IRow headerRow = sheet.CreateRow(0);
            if (DataTable.Rows.Count > 0)
            {
                //遍历属性集合生成excel的表头标题
                int colIndex = 0;
                foreach (OutputField item in Fields)
                {
                    ICell cell = null;

                    ICellStyle style = CreateStyle(workbook, item.HeadCellStyle);
                    cell = headerRow.CreateCell(colIndex);
                    cell.SetCellValue(item.FieldCaption);
                    cell.CellStyle = style;
                    colIndex++;
                }
                int rowIndex = 1;
                //遍历集合生成excel的行集数据
                for (int i = 0; i < DataTable.Rows.Count; i++)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    colIndex = 0;
                    foreach (OutputField item in Fields)
                    {
                        ICell cell = dataRow.CreateCell(colIndex);
                        if (item.FieldName == "<RowNum>")
                        {
                            cell.SetCellValue(i + 1);
                            if (item.DataCellStyle != null)
                            {
                                ICellStyle style = CreateStyle(workbook, item.DataCellStyle);
                                cell.CellStyle = style;
                            }
                        }
                        else
                        {
                            if (!string.IsNullOrEmpty(item.DataCellFormula))
                            {
                                Regex reg = new Regex("<RN>");
                                if (reg.IsMatch(item.DataCellFormula))
                                {
                                    string newFormula = reg.Replace(item.DataCellFormula, (rowIndex + 1).ToString());
                                    cell.SetCellFormula(newFormula);
                                }
                                else
                                {
                                    cell.SetCellFormula(item.DataCellFormula);
                                }
                            }
                            else
                            {
                                object field = DataTable.Rows[i][item.FieldName];
                                if (field != null)
                                {
                                    string value = field.ToString();
                                    value = value == "True" ? "是" : (value == "False" ? "否" : value);

                                    switch (item.DataType)
                                    {
                                        case EnumDataType.String:
                                            cell.SetCellValue(value);
                                            break;
                                        case EnumDataType.Int:
                                            int iValue = 0;
                                            if (int.TryParse(value, out iValue))
                                            {
                                                cell.SetCellValue(iValue);
                                            }
                                            else
                                            {
                                                cell.SetCellValue(0);
                                            }
                                            break;
                                        case EnumDataType.Float:
                                            float fValue = 0;
                                            if (float.TryParse(value, out fValue))
                                            {
                                                cell.SetCellValue(fValue);
                                            }
                                            else
                                            {
                                                cell.SetCellValue(0);
                                            }
                                            break;
                                        case EnumDataType.DataTime:
                                            DateTime dValue = DateTime.MinValue;
                                            if (DateTime.TryParse(value, out dValue))
                                            {
                                                cell.SetCellValue(dValue);
                                            }
                                            else
                                            {
                                                cell.SetCellValue("ERR");
                                            }
                                            break;
                                        default:
                                            break;
                                    }
                                }
                                else
                                {
                                    new Exception("字段" + item.FieldName + "不存在或拼写错误，请检查!");
                                }
                            }
                            if (item.DataCellStyle != null)
                            {
                                ICellStyle style = CreateStyle(workbook, item.DataCellStyle);
                                cell.CellStyle = style;
                            }
                        }
                        colIndex++;
                    }
                    rowIndex++;
                }
                if (!string.IsNullOrEmpty(description))
                {
                    rowIndex = rowIndex + 1;
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    ICellStyle style = CreateStyle(workbook, new FieldStyle()
                    {
                        Alignment = EnumAlignment.Left,
                        FontColorIndex = NPOI.HSSF.Util.HSSFColor.Red.Index
                    });
                    ICell cell = dataRow.CreateCell(0);
                    cell.SetCellValue(description);
                    cell.CellStyle = style;
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 5));
                }
            }
            for (int i = 0; i <= Fields.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            return ms;
        }
    }
}